package com.info.zhiduoduo.repository.mapper.company;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.account.ReceiptDto;
import com.info.zhiduoduo.repository.dto.company.CompanyRechargeDto;
import com.info.zhiduoduo.repository.entity.company.CompanyRecharge;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author bob
 * @since 2019-01-25
 */
public interface CompanyRechargeMapper extends BaseMapper<CompanyRecharge> {

	@Select(" select cr.*,c.compname,p.pay_name from zdd_company_recharge cr " +
			" join zdd_company c on c.compid = cr.compid " +
			" left join zdd_payment p on cr.pay_id = p.id " +
			" where cr.rechargeid = #{rechargeid} ")
	CompanyRechargeDto getRechargeDetail(@Param("rechargeid") Integer rechargeid);


	@Select(" SELECT cr.rechargeno, cr.createtime, c.kingdee_customer_code AS rechargeCompanyCode, " +
			" c.compname AS rechargeCompanyName, ccp.organization_code AS subjectCompanyOrganizationCode, " +
			" ccp.company_name AS subjectCompanyName, cr.amount, ccp.bank_card_number AS account " +
			" FROM zdd_company_recharge cr " +
			" LEFT JOIN zdd_crowdsource_company_party ccp ON ccp.id = cr.subject_id " +
			" LEFT JOIN zdd_company c ON c.compid = cr.compid " +
			" WHERE DATE(cr.createtime) BETWEEN DATE(#{startDate}) AND DATE(#{endDate}) ")
	List<ReceiptDto> queryReceiptDto(@Param("startDate") Date startDate, @Param("endDate") Date endDate);

	@Select(" select cr.*,c.compname,p.pay_name from zdd_company_recharge cr " +
			" join zdd_company c on c.compid = cr.compid " +
			" left join zdd_payment p on cr.pay_id = p.id " +
			" where if(#{state}=0,1=1,cr.state = #{state}) " +
			" and cr.createtime BETWEEN #{startDate} AND #{endDate} " +
			" and concat(cr.rechargeno,c.compname,cr.receiptno) like '%${name}%' " +
			" order by cr.rechargeid desc ")
	List<CompanyRechargeDto> getRechargeList(@Param("name") String name, @Param("state") String state, @Param("startDate") Date startDate, @Param("endDate") Date endDate);



	// DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')
	@Select(" SELECT r.* , p.company_name AS subjectCompanyName , p.organization_code AS subjectCompanyOrganizationCode,p.materiel_code as materielCode FROM zdd_company_recharge r " +
			" LEFT JOIN zdd_crowdsource_company_party p ON r.subject_id = p.id " +
			" WHERE r.compid = #{compid} " +
			" AND r.rechargetime <= #{endDate} " +
			" AND r.state = 3 " +
			" ORDER BY r.rechargeid DESC " +
			" LIMIT 1 ")
	ReceiptDto queryLastReceiptDto(@Param("compid")int compid , @Param("endDate")Date endDate);


	// DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')
	@Select(" SELECT " +
			" 	*  " +
			" FROM " +
			" `zdd_company_recharge` " +
			" WHERE compid = #{compid} " +
			" AND amount = #{amount}" +
			" AND DATE_FORMAT(createtime,'%Y-%m-%d %H:%i:%s') >= DATE_FORMAT(#{endDate},'%Y-%m-%d %H:%i:%s') " +
			" ORDER BY rechargeid DESC   " +
			"   LIMIT 1")
	CompanyRecharge equalAmount(@Param("compid")int compid , @Param("endDate")Date endDate,@Param("amount") String amount);
}
